package com.youmu.sichuansfc.action;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
	// 数据库用户名
	private static final String USERNAME = "youmu";
	// 数据库密码
	private static final String PASSWORD = "Youmu123";
	// 驱动信息
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	// 数据库地址
	private static final String URL = "jdbc:mysql://rm-2ze6082459gf45xa9.mysql.rds.aliyuncs.com:3306/youmu?useUnicode=true&characterEncoding=utf-8";

	private Connection connection;
	private PreparedStatement pstmt;
	private ResultSet resultSet;

	//线程绑定
	public static final ThreadLocal<JdbcUtil> session = new ThreadLocal<JdbcUtil>();

	public static JdbcUtil getInstance() {
		JdbcUtil jdbcUtil = null;
		jdbcUtil = (JdbcUtil) session.get();
		if (jdbcUtil == null) {
			jdbcUtil = new JdbcUtil();
			session.set(jdbcUtil);
		}
		return jdbcUtil;
	}

	private JdbcUtil() {
		// TODO Auto-generated constructor stub
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获得数据库的连接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		try {
			if (connection != null && !connection.isClosed()) {
				return connection;
			}
			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public Connection getJndiConnection() {
		try {
			if (connection != null && !connection.isClosed()) {
				return connection;
			}
			Context ic = new InitialContext();
			DataSource source = (DataSource) ic.lookup("java:comp/env/jdbc/iw");
			connection = source.getConnection();
		} catch (NamingException e) {
			System.out.println("数据源没找到！");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("获取数连接对象失败！");
			e.printStackTrace();
		}
		if(connection == null)
			return getConnection();
		return connection;
	}

	/**
	 * 增加、删除、改
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int updateByPreparedStatement(String sql, boolean autoCommit, Object... params) throws Exception{
		int len = -1;
		connection.setAutoCommit(autoCommit);
		pstmt = connection.prepareStatement(sql);
		int index = 1;
		if (params != null && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject(index++, params[i]);
			}
		}
		len = pstmt.executeUpdate();
		return len;
	}

	/**
	 * 查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> findSimpleResult(String sql, Object... params) {
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(index++, params[i]);
				}
			}
			resultSet = pstmt.executeQuery();// 返回查询结果
			ResultSetMetaData metaData = resultSet.getMetaData();
			int col_len = metaData.getColumnCount();
			if (resultSet.next()) {
				for (int i = 0; i < col_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * 查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResult(String sql, Object... params) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(index++, params[i]);
				}
			}
			resultSet = pstmt.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 只读取一个字段的查询
	 * */
	public <T> List<T> findMoreResultOneColumn(String sql, Object... params) {
		List<T> list = new ArrayList<T>();
		try {
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(index++, params[i]);
				}
			}
			resultSet = pstmt.executeQuery();
			while (resultSet.next()) {
				T key = (T) resultSet.getObject(1);
				list.add(key);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取seq
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public long nextSeq() {
		try {
			pstmt = connection.prepareStatement("select seq from iw_seq");
			resultSet = pstmt.executeQuery();
			if (resultSet.next()) {
				Long seq = resultSet.getLong(1);
				seq++;
				resultSet.close();
				pstmt.close();

				pstmt = connection.prepareStatement("update iw_seq set seq=?");
				pstmt.setLong(1, seq);
				pstmt.executeUpdate();
				return seq;
			} else {
				return 10000;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1L;
	}

	/**
	 * 通过反射机制查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> T findSimpleRefResult(String sql, Class<T> cls, Object... params) {
		T resultObject = null;
		try {
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(index++, params[i]);
				}
			}
			resultSet = pstmt.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				// 通过反射机制创建一个实例
				resultObject = cls.newInstance();
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					Field field = cls.getDeclaredField(cols_name);
					if (cols_value == null && field.getType().equals(String.class)) {
						cols_value = "";
					}
					field.setAccessible(true); // 打开javabean的访问权限
					field.set(resultObject, cols_value);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultObject;

	}

	/**
	 * 通过反射机制查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> findMoreRefResult(String sql, Class<T> cls, Object... params) {
		List<T> list = new ArrayList<T>();
		try {
			int index = 1;
			pstmt = connection.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(index++, params[i]);
				}
			}
			resultSet = pstmt.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				// 通过反射机制创建一个实例
				T resultObject = cls.newInstance();				
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null && cols_value instanceof String) {
						cols_value = "";
					}
					try {
						Field field = cls.getDeclaredField(cols_name);
						field.setAccessible(true); // 打开javabean的访问权限
						field.set(resultObject, cols_value);
					} catch (Exception e) {
						//字段不存在
					}
				}
				list.add(resultObject);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 释放数据库连接
	 */
	public void releaseConn() {
		try {
			if (resultSet != null) {
				resultSet.close();
				resultSet = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (connection != null) {
				connection.close();
				connection = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
